#!/usr/bin/perl

## Author: Shannon Eric Peevey <speeves@erikin.com>, 9 April 2004
# 
# Mssql.py 
# This file attacks the mssql servers, and creates dbs and users
# 
# Hopefully, this will be able to be replaced with Python someday
# It is only in perl, because the documentation for python and
# freetds suck!! ;(
#
# Here is the call made by Mssql.py to this file
#
# command = "/usr/bin/perl Mssql.pl '" + create + "' '" + web2mssqlHostname + "' '" 
#    + mssqlUser + "' '" + mssqlUserPassword + "' '" + mssqlMaintPlan 
#    + "' '" + database + "' '" + user + "' '" + password + "' '" + role "'"
##

use DBI;

my $rc;
my $sth;

($create, $mssqlHostname, $mssqlUser, $mssqlUserPassword, $mssqlMaintPlan, $database, $user, $password, $role ) = @ARGV;

# let's do the work...
# if ($create eq "dbcreate")
# elsif ($create eq "usercreate" || $create eq "addrole")
if ($create eq "dbcreate")
{
    $conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    $conn->do("use master");
    CreateDatabase($database);
    CreateAddlogin($database, $user, $password);
    $sth->finish;
    $conn->disconnect;
    
    #$conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    #$conn->do("use msdb");
    #AddMaintenancePlan($mssqlMaintPlan, $database);
    #$sth->finish;
    #$conn->disconnect;
    
    $conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    $conn->do("use " . $database);
    GrantDBAccess($user);
    $sth->finish;
    $conn->disconnect;

    # add the default user to db_datareader
    $role = "db_datareader";
    $conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    $conn->do("use " . $database);
    AddRoleMember($role, $user);
    $sth->finish;
    $conn->disconnect;
    
    # add the default user to db_datawriter
    $role = "db_datawriter";
    $conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    $conn->do("use " . $database);
    AddRoleMember($role, $user);
    $sth->finish;
    $conn->disconnect;
}
elsif ($create eq "usercreate" || $create eq "addrole")
{
    if($create eq "usercreate")
    {
	# Add user
	$conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
	$conn->do("use master");
	CreateAddlogin($database, $user, $password);
	$sth->finish;
	$conn->disconnect;    

	# Grant access to database
	$conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
	$conn->do("use " . $database);
	GrantDBAccess($user);
	$sth->finish;
	$conn->disconnect;
    }

    # Add user to role(s)
    $conn = Connect($mssqlHostname, $mssqlUser, $mssqlUserPassword);
    $conn->do("use " . $database);
    AddRoleMember($role, $user);
    $sth->finish;
    $conn->disconnect;
}

sub Connect 
{
    my $dbh = DBI->connect("dbi:Sybase:server=" . $mssqlHostname . ":1433", $mssqlUser, $mssqlUserPassword, {PrintError => 1});
    die "Unable for connect to server $DBI::errstr"
	unless $dbh;
    
    return $dbh;
}

sub CreateDatabase 
{ 
    $sth = $conn->prepare("CREATE DATABASE " . $database );
    $sth->execute;
}

sub CreateAddlogin 
{ 
    # let's create the remote connection database user
    $sth = $conn->prepare("sp_addlogin '" . $user . "', '" . $password . "', '" . $database . "'");
    $sth->execute;
}

sub AddMaintenancePlan 
{ 
    # let's add the database to the maintenance plan
    $sth = $conn->prepare("sp_add_maintenance_plan_db N'" . $mssqlMaintPlan . "', '" . $database . "'");
    $sth->execute;
}

sub GrantDBAccess 
{ 
    # let's grant this user access to the database
    $sth = $conn->prepare("sp_grantdbaccess '" . $user . "'");
    $sth->execute;
}

sub AddRoleMember 
{ 
    # let's add this user to the appropriate roles
    $sth = $conn->prepare("sp_addrolemember '" . $role . "', '" . $user . "'");
    $sth->execute;
}
